<?php

class department_Model_DbTable_DbDepartment extends Zend_Db_Table_Abstract
{
	protected $_name ="tbwu_department";
	
	public function getAllBranch(){
		$db = $this->getAdapter();
		$sql = "SELECT b.`branch_id`,b.`branch_name` FROM `tbwu_branch` AS b WHERE b.`status`=1";
		return $db->fetchAll($sql);
	}
	public function getFaculty($data_branch=null){
		$db = $this->getAdapter();
		$session_user = new Zend_Session_Namespace('auth');
		$user_id = $session_user->user_id;
		$branch_id = $session_user->branch;
		$user_type = $session_user->level;
		if($user_type==1){
			if($data_branch!=null){
				$where = " AND f.`branch_id` = $data_branch";
			}else{
				$where = "";
			}
		}else{
			$where = " AND f.`branch_id` = $branch_id";
		}
		$sql = "SELECT 
				  f.`faculty_id`,
				  f.`faculty_name`
				FROM
				  `tbwu_faculty` AS f 
				WHERE f.`status` = 1 $where 
				  ORDER BY f.`faculty_id`";
		return $db->fetchAll($sql);
	}
	public function getDepartments($data_branch=null){
		$db = $this->getAdapter();
		$session_user = new Zend_Session_Namespace('auth');
		$user_id = $session_user->user_id;
		$branch_id = $session_user->branch;
		$user_type = $session_user->level;
		if($user_type==1){
			if($data_branch!=null){
				$where = " AND f.`branch_id` = $data_branch";
			}else{
				$where = "";
			}
		}else{
			$where = " AND b.`branch_id`= $branch_id";
		}
		$sql = "SELECT 
				  d.`department_id`,
				  d.`department_name`,
				  d.`description`,
				  d.`created_date`,
				  d.`status`,
				  (SELECT f.faculty_name FROM `tbwu_faculty` AS f WHERE f.faculty_id = d.`faculty_id`) AS faculty_name,
				  b.`branch_name` 
				FROM
				  `tbwu_department` AS d,`tbwu_branch` AS b 
				WHERE d.`status` 
				AND b.`branch_id`=(SELECT f.branch_id FROM `tbwu_faculty` AS f WHERE f.faculty_id = d.`faculty_id`) $where 
				";
		return $db->fetchAll($sql);
	}
	public function getDepartmentById($id){
		$db = $this->getAdapter();
		$sql="SELECT 
				  d.`department_id`,
				  d.`department_name`,
				  d.`description`,
				  d.`faculty_id`,
				  d.`status`,
				  (SELECT f.`branch_id` FROM `tbwu_faculty` AS f WHERE f.`faculty_id`=d.`faculty_id`) AS branch_id
				FROM
				  `tbwu_department` AS d
				WHERE d.`department_id`=$id";
		return $db->fetchRow($sql);
	}
    public function addDepartment($data)
    {
    	$db= $this->getAdapter();
    	$db->beginTransaction();
    	$session_user = new Zend_Session_Namespace('auth');
    	$user_id = $session_user->user_id;
    	try {
	    	$arrDepartment = array(
	    		'faculty_id'		=>	$data["faculty"],
	    		'department_name'	=>	$data["department"],
	    		'description'		=>	$data["description"],
	    		'created_date'		=>	new Zend_Date(),
	    		'user_id'			=>	$user_id,
	    		'status'			=>	$data["status"]
	    	);
	    	$Department = $this->insert($arrDepartment);
	    $db->commit();
    	}catch (Exception $e){
    		$db->rollBack();
    		echo $e->getMessage();
    		exit();
    	}
    }
    public function editDepartment($data){
    	$db= $this->getAdapter();
    	$db->beginTransaction();
    	$session_user = new Zend_Session_Namespace('auth');
    	$user_id = $session_user->user_id;
    	try {
	    	$arrDepartment = array(
	    		'faculty_id'		=>	$data["faculty"],
	    		'department_name'	=>	$data["department"],
	    		'description'		=>	$data["description"],
	    		'created_date'		=>	new Zend_Date(),
	    		'user_id'			=>	$user_id,
	    		'status'			=>	$data["status"]
	    	);
	    	$where = $db->quoteInto("department_id=?", $data["id"]);
	    	$Department = $this->update($arrDepartment,$where);
	    $db->commit();
    	}catch (Exception $e){
    		$db->rollBack();
    		echo $e->getMessage();
    		exit();
    	}
    }
    public function deleteDepartment($id){
    	$db = $this->getAdapter();
    	$sql = "DELETE FROM tbwu_department WHERE department_id = $id";
    	$db->query($sql);
    }
}

